import pymysql
import logging
from pymysql.cursors import DictCursor
from dbutils.pooled_db import PooledDB

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class DBManager:
    def __init__(self, host='localhost', port=3306, user='root', password='123456', database='test'):
        try:
            # 创建数据库连接池
            self.pool = PooledDB(
                creator=pymysql,
                maxconnections=6,  # 连接池最大连接数
                mincached=2,       # 初始化时创建的连接数
                maxcached=5,       # 连接池最大空闲连接数
                maxshared=3,       # 共享连接数
                blocking=True,     # 连接数达到最大时是否阻塞
                host=host,
                port=port,
                user=user,
                password=password,
                database=database,
                charset='utf8mb4',
                cursorclass=DictCursor
            )
            logger.info("Database connection pool initialized successfully")
        except Exception as e:
            logger.error(f"Failed to initialize database connection pool: {str(e)}")
            raise

    def get_connection(self):
        """获取数据库连接"""
        return self.pool.connection()

    def execute_query(self, sql, params=None):
        """执行查询操作"""
        try:
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(sql, params)
                    result = cursor.fetchall()
                    return result
        except Exception as e:
            logger.error(f"Query execution failed: {str(e)}\nSQL: {sql}\nParams: {params}")
            raise

    def execute_many(self, sql, params_list):
        """批量执行SQL操作"""
        try:
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.executemany(sql, params_list)
                    conn.commit()
                    return cursor.rowcount
        except Exception as e:
            logger.error(f"Batch execution failed: {str(e)}\nSQL: {sql}")
            raise

    def execute_modify(self, sql, params=None):
        """执行修改操作（INSERT/UPDATE/DELETE）"""
        try:
            with self.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(sql, params)
                    conn.commit()
                    return cursor.rowcount
        except Exception as e:
            logger.error(f"Modification execution failed: {str(e)}\nSQL: {sql}\nParams: {params}")
            raise

    def insert(self, table, data):
        """插入数据"""
        fields = ", ".join(data.keys())
        values = ", ".join(["%s"] * len(data))
        sql = f"INSERT INTO {table} ({fields}) VALUES ({values})"
        return self.execute_modify(sql, list(data.values()))

    def update(self, table, data, condition):
        """更新数据"""
        set_clause = ", ".join([f"{k} = %s" for k in data.keys()])
        where_clause = " AND ".join([f"{k} = %s" for k in condition.keys()])
        sql = f"UPDATE {table} SET {set_clause} WHERE {where_clause}"
        params = list(data.values()) + list(condition.values())
        return self.execute_modify(sql, params)

    def delete(self, table, condition):
        """删除数据"""
        where_clause = " AND ".join([f"{k} = %s" for k in condition.keys()])
        sql = f"DELETE FROM {table} WHERE {where_clause}"
        return self.execute_modify(sql, list(condition.values()))

    def select(self, table, fields="*", condition=None, order_by=None, limit=None):
        """查询数据"""
        sql = f"SELECT {fields} FROM {table}"
        params = []

        if condition:
            where_clause = " AND ".join([f"{k} = %s" for k in condition.keys()])
            sql += f" WHERE {where_clause}"
            params.extend(condition.values())

        if order_by:
            sql += f" ORDER BY {order_by}"

        if limit:
            sql += f" LIMIT {limit}"

        return self.execute_query(sql, params)

    def execute_transaction(self, operations):
        """执行事务操作"""
        connection = self.get_connection()
        try:
            with connection.cursor() as cursor:
                for sql, params in operations:
                    cursor.execute(sql, params)
                connection.commit()
                return True
        except Exception as e:
            connection.rollback()
            logger.error(f"Transaction failed: {str(e)}")
            raise
        finally:
            connection.close()